package gwtappcontainer.server.apps.insight;

import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apis.APIException;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apis.insight.Practise;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class PractiseRepository {
	
	public static Practise get(String practiseName) {
        
	    try {
            String sql = "select practise_id, practise from practises where practise = ?";
           
            Practise practise = null;
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                       
                    ps.setString(1, practiseName.toLowerCase());
                   
                    try (ResultSet resultSet = ps.executeQuery()) {                                        
                        while (resultSet.next()) {
                            practise = new Practise();
                            practise.id = resultSet.getInt(1);
                            practise.name = resultSet.getString(2);
                        }
                    }
                }
            }
           
            return practise;
	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }              
	}
	
	public static Practise add(String practiseName) {
					
		 if (null != get(practiseName))
			 throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS, 
					 "Practise [" + practiseName + "] already exists");
		 
		 try {
		     String sql = "insert into practises values (0, ?)";                      
		    
		     try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
		            
		         try (PreparedStatement ps = connection.prepareStatement(sql)) {
		                
		             ps.setString(1, practiseName.toLowerCase());
		            
		             ps.executeUpdate();                                    
		         }
		     }
		    
		     Practise practise = get(practiseName);        
		     return practise;
		        
		 } catch (Exception ex) {
		     throw new RuntimeException(ex);
		 }              
	 }

	public static void delete(String practiseName) {
         
	     Practise practise = get(practiseName);
	    
	     if (null == practise)
	         throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
	                         "Practise [" + practiseName + "] does not exist");
	                                            
	     try {
	         String sql = "delete from practises where practise = ?";                        
	        
	         try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
	                
	             try (PreparedStatement ps = connection.prepareStatement(sql)) {
	                    
	                 ps.setString(1, practiseName.toLowerCase());
	                
	                 ps.executeUpdate();                                    
	             }
	         }                                                                      
	     } catch (Exception ex) {
	         throw new RuntimeException(ex);
	     }              
	 }

	public static ArrayList<Practise> getAll() {
		try {
	        String sql = "select practise_id, practise from practises order by practise";
	       
	        ArrayList<Practise> practises = new ArrayList<>();
	       
	        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
	               	        	
	            try (PreparedStatement ps = connection.prepareStatement(sql)) {	                   	               
	               
	                try (ResultSet resultSet = ps.executeQuery()) {                                        
	                    while (resultSet.next()) {
	                        Practise practise = new Practise();
	                        practise.id = resultSet.getInt(1);
	                        practise.name = resultSet.getString(2);
	                        
	                        practises.add(practise);
	                    }
	                }
	            }
	        }
	       
	        return practises;
	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    } 
	}
}

